package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.*;

public class ScheduleDB implements ScheduleDBIF{
	
	private  Connection con;
    /** Creates a new instance of DBEmployee */
    public ScheduleDB() {
      con = DbConnection1.getInstance().getDBcon();
    }
    
	public ArrayList<Schedule> getAllSchedules(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	public Schedule findSchedule(int schedId, boolean retrieveAssociation) {
		 String wClause = "  scheduleID = '" + schedId + "'";
	        return singleWhere(wClause, retrieveAssociation);
	}
	
	public int insertSchedule(Schedule sched) throws Exception {
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(scheduleID) from schedule");
        nextID = nextID + 1;
        System.out.println("next scheduleID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO Schedule(ScheduleID, ScheduleDate, starttime, endtime, SchedEmpId)  VALUES('"+
	  		nextID + "','" +
			sched.getDate()  + "','"  +
		    sched.getStartTime()  + "','"  +
		    sched.getEndTime()  + "','"  +
            sched.getEmployee().getEmpId() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new Schedule +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Schedule ikke oprettet");
          throw new Exception ("Schedule is not inserted correct");
       }
       return(rc);
	}
	
	public int updateSchedule(Schedule sched) {
		Schedule schedObj  = sched;
		int rc=-1;

		String query="UPDATE Schedule SET "+
		 	  "ScheduleDate ='"+ schedObj.getDate() + "', " +
              "StartTime ='"+ schedObj.getStartTime() + "', " +
               "EndTime ='"+ schedObj.getEndTime() + "', " +
               "ScheduleEmpId ='"+ schedObj.getEmployee().getEmpId() + "' " +
		          " WHERE ScheduleID = '"+ schedObj.getId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update Schedule
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Schedule db: "+ex);
	  	}
		return(rc);
	}

	public int deleteSchedule(int schedID) {
		int rc=-1;
		  
	  	String query="DELETE FROM Schedule WHERE scheduleID = '" +
				schedID + "'";
                System.out.println(query);
	  	try{ // delete from schedule
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in schedule db: "+ex);
   	        }
		return(rc);
	}
	
	private Schedule singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Schedule sObj = new Schedule();
        String query =  buildQuery(wClause);
        System.out.println("DbSchedule -singleWhere " + query); 
        try{ // read from Event
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       sObj = buildSchedule(results);  
           }//end if
	   stmt.close();
	   if(retrieveAssociation)
       {   //The Employee is to be built
		   int empId = sObj.getEmployee().getEmpId();
           EmployeeDB dbEmp= new EmployeeDB();
           Employee e = dbEmp.findEmployee(empId, false);
           sObj.setEmployee(e);
           System.out.println("Employee is seleceted");
	}//slut try	
        else{ //no order was found
            sObj = null;
        }
	}//slut try	
	catch(Exception e){
	   System.out.println("Query exception - select Schedule : "+e);
	   e.printStackTrace();
        }
	return sObj;
    }
	
	 //miscWhere is used when more than one Event is selected and build
    private ArrayList<Schedule> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Schedule> list = new ArrayList<Schedule>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbSchedule " + query); 
  		try{ // read from Schedule
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
		     	 Schedule sObj = new Schedule();
			 sObj = buildSchedule(results);	
                         list.add(sObj);
                         //missing test on retriveAssociation
			}//end while
			stmt.close();
			if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(Schedule sObj : list){
                	int empId = sObj.getEmployee().getEmpId();
                    EmployeeDB dbEmp= new EmployeeDB();
                    Employee e = dbEmp.findEmployee(empId, false);
                    sObj.setEmployee(e);
                    System.out.println("Employee is seleceted");
                }
            }
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select Schedule : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Schedule buildSchedule(ResultSet results)
    {
       Schedule sObj = new Schedule();
       Employee empObj = new Employee();
       
         try{
               sObj.setId(results.getInt("scheduleID"));
               sObj.setDate(results.getString("scheduledate"));
               sObj.setStartTime(results.getString("starttime"));
               sObj.setEndTime(results.getString("endtime"));
               empObj.setEmpId(results.getInt("ScheduleEmpId"));
               sObj.setEmployee(empObj);
               
          }
         catch(Exception e)
         {
             System.out.println("building Schedule object");
         }
        
        return sObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT scheduleid, scheduleDate, StartTime, EndTime, ScheduleEmpId FROM Schedule, Employee where scheduleEmpId = EmpId"; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}
}
